REM  SQL scripts for Chapter 8 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


create procedure MY_RAISE (My_Emp_No IN NUMBER, Raise IN NUMBER)
as begin
      update EMPLOYEE
         set Salary = Salary+Raise
       where Empno = My_Emp_No;
end;
/

alter procedure MY_RAISE compile;


create index CITY_ST_ZIP_NDX
on EMPLOYEE(City, State, Zip)
tablespace INDEXES;

select * from EMPLOYEE
 where State='NJ';


select * from EMPLOYEE
where Empno between 1 AND 100;


explain plan
set Statement_Id = 'TEST'
for
select * from EMPLOYEE
where City > 'Y%';


select
  LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name
   Q_Plan
  from PLAN_TABLE
 where Statement_Id = 'TEST'
connect by prior ID = Parent_ID and Statement_ID = 'TEST'
 start with ID=0;

analyze table COMPANY compute statistics;

analyze table COMPANY compute statistics for table 
    for indexed columns;


execute DBMS_UTILITY.ANALYZE_SCHEMA(`APPOWNER',`COMPUTE');


select
      Tablespace_Name,   /*Tablespace name*/
      Owner,             /*Owner of the segment*/
      Segment_Name,      /*Name of the segment*/
      Segment_Type,      /*Type of segment (ex. TABLE, INDEX)*/
      Extents,           /*Number of extents in the segment*/
      Blocks,            /*Number of db blocks in the segment*/
      Bytes              /*Number of bytes in the segment*/
from DBA_SEGMENTS
/

select
      Tablespace_Name,   /*Tablespace name*/
      Owner,             /*Owner of the segment*/
      Segment_Name,      /*Name of the segment*/
      Segment_Type,      /*Type of segment (ex. TABLE, INDEX)*/
      Extent_ID,         /*Extent number in the segment*/
      Block_ID,          /*Starting block number for the extent*/
      Bytes,             /*Size of the extent, in bytes*/
      Blocks             /*Size of the extent, in Oracle blocks*/
 from DBA_EXTENTS
where Segment_Name = 'segment_name'
order by Extent_ID;

exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y

imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y


rem
rem  file: fsfi.sql
rem  location: /orasw/dba/contig
rem
rem  This script measures the fragmentation of free space
rem  in all of the tablespaces in a database and scores them
rem  according to an arbitrary index for comparison.
rem
set newpage 0 pagesize 60
column fsfi format 999.99

select
      Tablespace_Name,
      SQRT(MAX(Blocks)/SUM(Blocks))*
      (100/SQRT(SQRT(COUNT(Blocks)))) Fsfi
from DBA_FREE_SPACE
group by
      Tablespace_Name
order by 1

spool fsfi.lis
/
spool off


rem
rem   file: mapper.sql
rem   location: /orasw/dba/contig
rem   Parameters: the tablespace name being mapped
rem
rem   Sample invocation:
rem   @mapper DEMODATA
rem
rem   This script generates a mapping of the space usage
rem   (free space vs used) in a tablespace. It graphically
rem   shows segment and free space fragmentation.
rem
set pagesize 60 linesize 132 verify off
column file_id heading "File|Id"

select
      'free space' Owner,    /*"owner" of free space*/
      '   ' Object,          /*blank object name*/
      File_ID,               /*file ID for the extent header*/
      Block_ID,              /*block ID for the extent header*/
      Blocks                 /*length of the extent, in blocks*/
 from DBA_FREE_SPACE
where Tablespace_Name = UPPER('&&1')
union
select
      SUBSTR(Owner,1,20),         /*owner name (first 20 chars)*/
      SUBSTR(Segment_Name,1,32),  /*segment name*/
      File_ID,                    /*file ID for extent header*/
      Block_ID,                   /*block ID for block header*/
      Blocks                    /*length of the extent in blocks*/
 from DBA_EXTENTS
where Tablespace_Name = UPPER('&&1')
order by 3,4

spool &&1._map.lst
/
spool off
undefine 1



alter tablespace DEMONDX 
default storage (pctincrease 1);


alter tablespace DEMONDX coalesce;

analyze table TABLE_NAME list chained rows into CHAINED_ROWS;


select
       Owner_Name,      /*Owner of the data segment*/
       Table_Name,      /*Name of the table with the chained rows*/
       Cluster_Name,    /*Name of the cluster, if it is clustered*/
       Head_RowID       /*Rowid of the first part of the row*/
from CHAINED_ROWS;


truncate table EMPLOYEE drop storage;

truncate cluster EMP_DEPT reuse storage;

alter table EMPLOYEE
truncate partition PART3
drop storage;


set copycommit 1
set arraysize 1000
copy from HR/PUFFINSTUFF@loc -
create EMPLOYEE -
using -
select * from EMPLOYEE -
where State = 'NM'


create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'loc';


select * from EMPLOYEE@HR_LINK;


select DEPT, MAX(Salary)
  from EMPLOYEE@HR_LINK
 group by DEPT;


create snapshot LOCAL_EMP
pctfree 5
tablespace data_2
storage (initial 100K next 100K pctincrease 0)
refresh fast
      start with SysDate
      next SysDate+7
as select * from EMPLOYEE@HR_LINK;


create snapshot log on EMPLOYEE
tablespace DATA
storage (initial 10K next 10K pctincrease 0);



create procedure MY_RAISE (My_Emp_No IN NUMBER, Raise IN NUMBER)
as begin
      update EMPLOYEE@HR_LINK
      set Salary = Salary+Raise
      where Empno = My_Emp_No;
end;
/


execute MY_RAISE@HR_LINK(1234,2000);


create synonym MY_RAISE for MY_RAISE@HR_LINK;

execute MY_RAISE(1234,2000);


